/**
 * @copyright 2018 sinping.com 北京芯平科技股份有限公司. All rights reserved.
 * 本内容仅限于北京芯平科技股份有限公司内部传阅，禁止外泄以及用于其他的商业目的.
 */
package com.sinping.qh.utils.excel;

import com.sinping.qh.utils.collection.ListUtil;
import com.sinping.qh.utils.collection.MapUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 创建CSV
 *
 * @author 陈长石
 * @create 2018/1/23
 */
public class CreateCSV {

    private final static Logger logger = LoggerFactory.getLogger(CreateCSV.class);

    /**
     * 创建CSV，没有序号
     *
     * @param head
     * @param content
     * @param dtos
     * @param clazz
     * @param response
     */
    public static void exportCsv(String[] head, String[] content, List dtos, Class clazz, String fileName, HttpServletResponse response){
        //exportCsvBase(head, content, dtos, clazz, fileName, response, false);
        exportExcelBase(head, content, dtos, clazz, fileName, response, false);
    }

    /**
     * 创建CSV，有序号
     *
     * @param head
     * @param content
     * @param dtos
     * @param clazz
     * @param response
     */
    public static void exportCsvNo(String[] head, String[] content, List dtos, Class clazz, String fileName, HttpServletResponse response){
        //exportCsvBase(head, content, dtos, clazz, fileName, response, true);
        exportExcelBase(head, content, dtos, clazz, fileName, response, true);
    }

    /**
     * 导出Excel
     */
    public static void exportExcelBase(String[] headersName, String[] headersId, List dtoList, Class tCls,
                                       String fileName, HttpServletResponse response, boolean isNo) {
        ServletOutputStream exportXls = null;
        HSSFWorkbook wb = null;
        try {
            //响应头
            fileName = fileName.substring(0, fileName.indexOf("."));
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName+".xls", "utf-8"));
            response.setContentType("application/vnd.ms-excel");
            exportXls = response.getOutputStream();

            wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(fileName);
            sheet.setDefaultColumnWidth((short)15);

            //标题
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            HSSFRow row = sheet.createRow(0);

            String [] newLentHeadName = null;
            if(isNo){
                HSSFCell cell = row.createCell(0);
                cell.setCellValue("序号");
                cell.setCellStyle(style);

                int length = headersName.length;
                newLentHeadName = new String [length + 1];
                for (int i=0; i<headersName.length; i++) {
                    newLentHeadName[i+1] = headersName[i];
                }
            }
            for (int i=0; i<headersName.length; i++) {
                int index;
                if(isNo){
                    index = i+1;
                }else{
                    index = i;
                }
                HSSFCell cell = row.createCell(index);
                if(isNo){
                    cell.setCellValue(newLentHeadName[index]);
                } else {
                    cell.setCellValue(headersName[index]);
                }

                cell.setCellStyle(style);
            }

            //汇总字段
//            Map<String, Double> sumColumn = new HashMap<>();
//            for(int i=0; i<headersId.length; i++){
//                String column = headersId[i];
//                if(column.endsWith("100") || column.endsWith("IDR") || column.endsWith("USD")){
//                    //金额类字段，汇总
//                    sumColumn.put(i+"", 0.0);
//                }
//            }

            //内容
            SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat formater2 = new SimpleDateFormat("yyyy-MM-dd");
            DecimalFormat decimalFormat = new DecimalFormat("######0.00");
            DecimalFormat decimalFormat2 = new DecimalFormat("######0.0000");

            for (int i=0; i<dtoList.size(); i++) {
                Object dto = dtoList.get(i);
                row = sheet.createRow(i+1);

                if(isNo){
                    row.createCell(0).setCellValue(""+(i+1));
                }

                for(int j=0; j<headersId.length; j++) {
                    String column = headersId[j];
                    String tag = null;
                    if(column.indexOf("/") > 0){
                        String[] columns = column.split("/");
                        column = columns[0];
                        tag = columns[1];
                    }

                    boolean isNegative = false;
                    if(column.charAt(0) == '-'){
                        column = column.substring(1);
                        isNegative = true;
                    }

                    String mapName = "";
                    if(column.indexOf(":") > 0){
                        String[] columns = column.split(":");
                        column = columns[0];
                        mapName = columns[1];
                    }

                    Field field = getDeclaredField(tCls, column);
                    Object value = field.get(dto);

                    Object causeValue = "";
                    try {
                        Field causeField = getDeclaredField(tCls, "cause");
                        causeValue = causeField.get(dto);
                    }catch (Exception e){
                        //logger.error(e.getMessage(), e);
                    }

                    if(tag != null && value != null){
                        if("date".equals(tag)){
                            value = formater.format((Date)value);
                        }if("date2".equals(tag)){
                            value = formater2.format((Date)value);
                        }else if("type".equals(tag)){
                            value = typeConvert(column, mapName, value+"");
                        }/*else if("CNY".equals(tag)){
                            value = cnyConvert(causeValue+"", column, value+"")+"\t";
                        }*/else if("IDR".equals(tag)){
                            value = idrConvert(causeValue+"", column, value+"")+"\t";
                        }else if("USD".equals(tag)){
                            value = usdConvert(causeValue+"", column, value+"")+"\t";
                        }else if("100".equals(tag)){
                            value = decimalFormat.format(Long.valueOf(value+"").longValue()/100.0)+"\t";
                        }else if("10000".equals(tag)){
                            value = decimalFormat2.format(Long.valueOf(value+"").longValue()/10000.0)+"\t";
                        }else if("rate".equals(tag)){
                            value = decimalFormat.format(Long.valueOf(value+"").longValue()/10000.0)+"%";
                        }else if("text".equals(tag)){
                            //value = value;
                        }

                        if(isNegative){
                            value = "-"+value;
                        }

//                        if(value!= null && StringUtils.isNotBlank(value+"") && sumColumn.containsKey(j+"")){
//                            sumColumn.put(j+"", sumColumn.get(j+"").doubleValue()+Double.valueOf(value+"").doubleValue());
//                        }
                    }

                    HSSFCell cell = null;
                    if(isNo) {
                        cell = row.createCell(j+1);
                    }else{
                        cell = row.createCell(j);
                    }

                    if(value == null) {
                        //默认占位符
                        cell.setCellValue("");
                    }else{
                        String strValue = String.valueOf(value);
                        if(strValue.indexOf("\t") >= 0){
                            //转换为数值类型
                            strValue = strValue.replace("\t", "");
                            if(StringUtils.isNotBlank(strValue)) {
                                cell.setCellValue(Double.valueOf(strValue));
                            }
                        }else{
                            //字符串类型
                            cell.setCellValue(strValue);
                        }
                    }
                }
            }

            //汇总
//            if(ListUtil.isNotEmpty(dtoList) && MapUtil.isNotEmpty(sumColumn)) {
//                row = sheet.createRow(dtoList.size()+1);
//
//                HSSFCell cell = row.createCell(0);
//                cell.setCellValue("合计");
//
//                for (int j = 0; j < headersId.length; j++) {
//                    if (sumColumn.containsKey(j + "")) {
//                        Double value = sumColumn.get(j + "");
//
//                        if(isNo) {
//                            cell = row.createCell(j+1);
//                        }else{
//                            cell = row.createCell(j);
//                        }
//                        cell.setCellValue(value);
//                    }
//                }
//            }

            wb.write(exportXls);
            wb.close();
            logger.info("导出成功!");

        } catch (Exception e) {
            logger.error("导出失败!", e);
        } finally {
            try {
              //  wb.d();
                exportXls.close();
            }catch (IOException e) {
            }
        }
    }

    /**
     * 导出CSV
     *
     * @param head
     * @param content
     * @param dtos
     * @param clazz
     * @param fileName
     * @param response
     * @param isNo 是否有序号
     */
    public static void exportCsvBase(String[] head, String[] content, List dtos, Class clazz, String fileName, HttpServletResponse response, boolean isNo){
        //PrintWriter printWriter = null;
        ServletOutputStream outputStream = null;
        try {
            if(head!=null && content!=null && head.length==content.length) {

                String valueSplit = ",";
                String lineSplit = "\n";
                String encoding = "GBK";

                response.setCharacterEncoding(encoding);
                response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
                response.setContentType("application/csv;charset="+encoding);
                outputStream = response.getOutputStream();//getWriter();

                //指定UTF-8+BOM编码格式
                //byte[] bom = {(byte)0xEF, (byte)0xBB, (byte)0xBF};
                //outputStream.write(bom);

                //标题
                StringBuilder builder = null;
                if(isNo){
                    builder = new StringBuilder("序号");
                }else{
                    builder = new StringBuilder();
                }
                for (String temp : head) {
                    if(builder.length() > 0){
                        builder.append(valueSplit);
                    }
                    builder.append(temp);
                }
                builder.append(lineSplit);
                outputStream.write(builder.toString().getBytes(encoding));

                //汇总字段
                Map<String, Double> sumColumn = new HashMap<>();
                for(int i=0; i<content.length; i++){
                    String column = content[i];
                    if(column.endsWith("100") || column.endsWith("IDR") || column.endsWith("USD")){
                        //金额类字段，汇总
                        sumColumn.put(i+"", 0.0);
                    }
                }

                //内容
                SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                SimpleDateFormat formater2 = new SimpleDateFormat("yyyy-MM-dd");
                DecimalFormat decimalFormat = new DecimalFormat("######0.00");
                DecimalFormat decimalFormat2 = new DecimalFormat("######0.0000");
                StringBuilder builder2 = new StringBuilder();
                boolean isFirst = true;
                for (int i=0; i<dtos.size(); i++) {
                    Object dto = dtos.get(i);

                    isFirst = true;

                    if(isNo){
                        builder2.append(i+1);
                    }

                    for(int j=0; j<content.length; j++) {

                        String column = content[j];
                        String tag = null;
                        if(column.indexOf("/") > 0){
                            String[] columns = column.split("/");
                            column = columns[0];
                            tag = columns[1];
                        }

                        boolean isNegative = false;
                        if(column.charAt(0) == '-'){
                            column = column.substring(1);
                            isNegative = true;
                        }

                        String mapName = "";
                        if(column.indexOf(":") > 0){
                            String[] columns = column.split(":");
                            column = columns[0];
                            mapName = columns[1];
                        }

                        Field field = getDeclaredField(clazz, column);
                        Object value = field.get(dto);
                        if(isNo) {
                            builder2.append(valueSplit);
                        }else{
                            if (isFirst) {
                                isFirst = false;
                            } else {
                                builder2.append(valueSplit);
                            }
                        }

                        Object causeValue = "";
                        try {
                            Field causeField = getDeclaredField(clazz, "cause");
                            causeValue = causeField.get(dto);
                        }catch (Exception e){
                            //logger.error(e.getMessage(), e);
                        }

                        if(tag != null && value != null){
                            if("date".equals(tag)){
                                value = formater.format((Date)value);
                            }if("date2".equals(tag)){
                                value = formater2.format((Date)value);
                            }else if("type".equals(tag)){
                                value = typeConvert(column, mapName, value+"");
                            }/*else if("CNY".equals(tag)){
                                value = cnyConvert(causeValue+"", column, value+"")+"\t";
                            }*/else if("IDR".equals(tag)){
                                value = idrConvert(causeValue+"", column, value+"")+"\t";
                            }else if("USD".equals(tag)){
                                value = usdConvert(causeValue+"", column, value+"")+"\t";
                            }else if("100".equals(tag)){
                                value = decimalFormat.format(Long.valueOf(value+"").longValue()/100.0)+"\t";
                            }else if("10000".equals(tag)){
                                value = decimalFormat2.format(Long.valueOf(value+"").longValue()/10000.0)+"\t";
                            }else if("rate".equals(tag)){
                                value = decimalFormat.format(Long.valueOf(value+"").longValue()/10000.0)+"%\t";
                            }else if("text".equals(tag)){
                                value = value+"\t";
                            }

                            if(isNegative){
                                value = "-"+value;
                            }

                            if(value!= null && StringUtils.isNotBlank(value+"") && sumColumn.containsKey(j+"")){
                                sumColumn.put(j+"", sumColumn.get(j+"").doubleValue()+Double.valueOf(value+"").doubleValue());
                            }
                        }
                        builder2.append(value==null?"--":value);
                    }
                    builder2.append(lineSplit);
                }
                outputStream.write(builder2.toString().getBytes(encoding));

                //汇总
                if(ListUtil.isNotEmpty(dtos) && MapUtil.isNotEmpty(sumColumn)) {
                    StringBuilder builder3 = new StringBuilder("合计");
                    if (isNo) {
                        builder3.append(valueSplit);
                    }
                    for (int j = 0; j < content.length; j++) {
                        if (j != 0) {
                            builder3.append(valueSplit);
                        }
                        if (sumColumn.containsKey(j + "")) {
                            Double value = sumColumn.get(j + "");
                            builder3.append(decimalFormat.format(value)+"\t");
                        }
                    }
                    outputStream.write(builder3.toString().getBytes(encoding));
                }
            }
        }catch (NoSuchFieldException e) {
            logger.error("该字段不存在", e);
        } catch (IllegalAccessException e) {
            logger.error("对象访问异常", e);
        } catch (IOException e) {
            logger.error("IO异常", e);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            if(outputStream != null) {
                try {
                    outputStream.close();
                }catch(Exception e){}
            }
        }
    }

    /**
     * 循环获取父类的字段
     *
     * @param clazz
     * @param fieldName
     * @return
     * @throws Exception
     */
    public static Field getDeclaredField(Class clazz, String fieldName) throws Exception {
        Field field = null ;
        while(field == null) {
            try {
                field = clazz.getDeclaredField(fieldName) ;
                field.setAccessible(true);
                return field ;
            } catch (Exception e) {
                clazz = clazz.getSuperclass();
                if(clazz == null){
                    throw new Exception("无法找到名字为"+fieldName+"的字段");
                }
                //这里甚么都不要做！并且这里的异常必须这样写，不能抛出去。
                //如果这里的异常打印或者往外抛，则就不会执行clazz = clazz.getSuperclass(),最后就不会进入到父类中了
            }
        }
        return null;
    }

    /**
     * 支付金额（CNY）
     * @param causeValue
     * @param column
     * @param value
     * @return
     */
    /*public static String cnyConvert(String causeValue, String column, String value){
        if(causeValue==null || StringUtils.isBlank(causeValue+"") || StringUtils.isBlank(column) ||StringUtils.isBlank(value)){
            return value;
        }
        if(!"payAmount".equals(column)){
            return value;
        }
        if(causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_BANKCARD+"")
                || causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_WECHAT+"")
                || causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_ALIPAY+"")
                || causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_OFFLINE+"")
                || causeValue.equals(Common.CAUSE_BALANCE_WITHDRAW_BANKCARD+"")){
            //以上类型订单的支付金额为人民币
            DecimalFormat decimalFormat = new DecimalFormat("######0.00");
            return decimalFormat.format(Long.valueOf(value)/100.0);
        }else{
            return "";
        }
    }*/

    /**
     * 支付金额（IDR）
     * @param causeValue
     * @param column
     * @param value
     * @return
     */
    public static String idrConvert(String causeValue, String column, String value){
        if(causeValue==null || StringUtils.isBlank(causeValue+"") || StringUtils.isBlank(column) ||StringUtils.isBlank(value)){
            return value;
        }
        if(!"payAmount".equals(column)){
            return value;
        }
       /* if(causeValue.equals(Common.CAUSE_BALANCE_IN_DEPOSIT_TELTRANSFER+"")
                || causeValue.equals(Common.CAUSE_BALANCE_IN_WITHDRAW_TELTRANSFER+"")){
            //以上类型订单的支付金额为印尼盾
            DecimalFormat decimalFormat = new DecimalFormat("######0.00");
            return decimalFormat.format(Long.valueOf(value)/100.0);
        }*/else{
            return "";
        }
    }

    /**
     * 支付金额（USD）
     * @param causeValue
     * @param column
     * @param value
     * @return
     */
    /*public static String usdConvert(String causeValue, String column, String value){
        if(causeValue==null || StringUtils.isBlank(causeValue+"") || StringUtils.isBlank(column) ||StringUtils.isBlank(value)){
            return value;
        }
        if(!"payAmount".equals(column)){
            return value;
        }
        if(causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_BANKCARD+"")
                || causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_WECHAT+"")
                || causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_ALIPAY+"")
                || causeValue.equals(Common.CAUSE_BALANCE_DEPOSIT_OFFLINE+"")
                || causeValue.equals(Common.CAUSE_BALANCE_WITHDRAW_BANKCARD+"")){
            return "";
        }else{
            //非以上类型订单的支付金额为美元
            DecimalFormat decimalFormat = new DecimalFormat("######0.00");
            return decimalFormat.format(Long.valueOf(value)/100.0);
        }
    }*/

    /**
     * 支付金额（USD）
     * @param causeValue
     * @param column
     * @param value
     * @return
     */
    public static String usdConvert(String causeValue, String column, String value){
        if(causeValue==null || StringUtils.isBlank(causeValue+"") || StringUtils.isBlank(column) ||StringUtils.isBlank(value)){
            return value;
        }
        if(!"payAmount".equals(column)){
            return value;
        }
        /*if(causeValue.equals(Common.CAUSE_BALANCE_IN_DEPOSIT_TELTRANSFER+"")
                || causeValue.equals(Common.CAUSE_BALANCE_IN_WITHDRAW_TELTRANSFER+"")){
            return "";
        }*/else{
            //非以上类型订单的支付金额为美元
            DecimalFormat decimalFormat = new DecimalFormat("######0.00");
            return decimalFormat.format(Long.valueOf(value)/100.0);
        }
    }

    /**
     * 转换类型
     * @param column
     * @param mapName
     * @param value
     * @return
     */
    public static String typeConvert(String column, String mapName, String value){
        if(StringUtils.isNotBlank(mapName)){
            column = mapName;
        }
        if(allMap.containsKey(column)){
            Map<Object, String> valueMap = allMap.get(column);
            String valueStr = valueMap.get(value);
            if(StringUtils.isNotBlank(valueStr)){
                return valueStr;
            }
        }
        return value;
    }


    /**
     * 类型转换映射对象
     */
    public static Map<Object, String> fundTypeMap = new HashMap<>();
    public static Map<Object, String> causeMap = new HashMap<>();
    public static Map<Object, String> orderStatusMap = new HashMap<>();

//    static {
//
//        causeMap.put(Common.CAUSE_BALANCE_DEPOSIT_BANKCARD + "", "入金-银行卡");
//        causeMap.put(Common.CAUSE_BALANCE_DEPOSIT_WECHAT + "", "入金-微信支付");
//        causeMap.put(Common.CAUSE_BALANCE_DEPOSIT_ALIPAY + "", "入金-支付宝");
//        causeMap.put(Common.CAUSE_BALANCE_DEPOSIT_WITHDRAW_RETURN + "", "入金-出金退回");
//        causeMap.put(Common.CAUSE_BALANCE_WITHDRAW_BANKCARD + "", "出金-银行卡");
//        causeMap.put(Common.CAUSE_BALANCE_INTEREST + "", "获得利息");
//        causeMap.put(Common.CAUSE_BALANCE_COMMISSION + "", "获得佣金");
//        causeMap.put(Common.CAUSE_BALANCE_BONUS + "", "公司发放奖金");
//        causeMap.put(Common.CAUSE_BALANCE_SALARY + "", "公司发放工资");
//
//
//        orderStatusMap.put(OrderCommon.ORDER_STATUS_SUBMITTED + "", "已提交");
//        orderStatusMap.put(OrderCommon.ORDER_STATUS_PROCESSING + "", "处理中");
//        orderStatusMap.put(OrderCommon.ORDER_STATUS_IGNORED + "", "已忽略");
//        orderStatusMap.put(OrderCommon.ORDER_STATUS_RETURNED + "", "已退回");
//        orderStatusMap.put(OrderCommon.ORDER_STATUS_CANCELED + "", "已取消");
//        orderStatusMap.put(OrderCommon.ORDER_STATUS_FINISHED + "", "已完成");
//
//
//        fundTypeMap.put(Common.FUND_TYPE_DEPOSIT + "", "入金");
//        fundTypeMap.put(Common.FUND_TYPE_WITHDRAW + "", "出金");
//        fundTypeMap.put(Common.FUND_TYPE_INTEREST + "", "利息");
//        fundTypeMap.put(Common.FUND_TYPE_COMMISSION + "", "佣金");
//        fundTypeMap.put(Common.FUND_TYPE_REWARD + "", "提成");
//        fundTypeMap.put(Common.FUND_TYPE_COMPANY_PAYMENT + "", "公司发放工资或者奖金");
//
//
//
//    }

    public static Map<String, Map<Object, String>> allMap = new HashMap<>();

    static {
        allMap.put("cause", causeMap);
        allMap.put("orderStatus", orderStatusMap);
        allMap.put("fundType", fundTypeMap);
    }

}